# 将 csv 数据导入数据库

import sqlite3
import csv

class data_import():
    # 连接数据库
    def __init__(self):
        self.conn = sqlite3.connect("../dataStorage/zhaopin.db")
        self.cursor = self.conn.cursor()
        sql_table = """
            create table if not exists dictionary(
            id integer,
            name varchar(50),
            abb varchar(50),
            primary key (id)
        );
        """
        self.cursor.execute(sql_table)

    # 创建数据表
    def createTable(self, tableName):
        sql_table=f"""
        create table if not exists {tableName}(
            id integer,
            positionName varchar(50),
            corporateName varchar(50),
            corporateType varchar(50),
            companySize varchar(50),
            monthlyPay varchar(50),
            address varchar(50),
            MaxworkExperience varchar(50),
            MinworkExperience varchar(50),
            diploma varchar(50),
            technology varchar(50),
            primary key (id)
        );
        """
        self.cursor.execute(sql_table)

    # 写入数据
    def write_to_db(self, csv_path, tableName, dataname):
        self.createTable(tableName)
        sql = f"""
            insert into {tableName} (address,companySize,corporateName,corporateType,diploma,monthlyPay,positionName,technology,minWorkExperience,maxWorkExperience)
            values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """
        with open(csv_path, encoding='utf-8') as f:
            data = []
            count = 0
            if_header_csv = True
            for row in csv.reader(f, skipinitialspace=True):
                if if_header_csv:
                    if_header_csv = False
                    continue
                data.append(row)
                count += 1
                if count >= 100:
                    self.cursor.executemany(sql,data)
                    self.conn.commit()
                    data = []
                    count = 0
            self.cursor.executemany(sql,data)
            self.conn.commit()
            data = []
            count = 0
        
        sql = f"""
            insert into dictionary (name, abb) 
            values('{dataname}','{tableName}')
        """
        self.cursor.execute(sql)
        self.conn.commit()
        
    # 关闭数据库
    def close(self):
        self.conn.close()

if __name__ == "__main__":
    db = data_import()
    db.write_to_db("../dataPre/Hadoop_handle.csv", "Hadoop", "Hadoop")
    db.write_to_db("../dataPre/Java_handle.csv", "Java", "Java")
    db.write_to_db("../dataPre/人工智能_handle.csv", "AI", "人工智能")
    db.write_to_db("../dataPre/python_handle.csv", "Python", "Python")
    db.write_to_db("../dataPre/深度学习_handle.csv", "DL", "深度学习")
    db.write_to_db("../dataPre/数据分析师_handle.csv", "DA", "数据分析师")
    db.write_to_db("../dataPre/数据可视化_handle.csv", "DV", "数据可视化")
    db.write_to_db("../dataPre/数据挖掘_handle.csv", "DM", "数据挖掘")
    db.write_to_db("../dataPre/数据标注_handle.csv", "DAn", "数据标注")
    db.write_to_db("../dataPre/智能制造_handle.csv", "IM", "智能制造")
    db.write_to_db("../dataPre/机器学习_handle.csv", "ML", "机器学习")
    db.write_to_db("../dataPre/机器视觉_handle.csv", "MV", "机器视觉")
    db.write_to_db("../dataPre/计算机视觉_handle.csv", "CV", "计算机视觉")

    db.close()
    